Database Restore

This page covers how to manually restore DEACOM SQL Databases. When restoring production data into test\training databases, there are several critical steps that are required. All users must be logged out of the database that is being restored. It is recommended users stay logged into Production while Test\Training is being restored to prevent any accidental restore over production databases. Additionally, restores should only be performed with backups created from the same DEACOM build version. If using EDI and Triggers, they must be modified as they will be pointing to the Production data rather than Test. When refreshing your test environment prior to updating production for testing purposes, be sure to completely restore all test databases including the system database to avoid problems with updating.

  1. To refresh Test/Training folder that is not on the same version
  2. Rename the existing test\training folder.
  3. Copy the production folder and paste.
  4. Rename to previous test\training folder name.
  5. Copy deacom.ini from previous test\training folder and paste in new training folder or modify deacom.ini to point to the proper file paths, sql server, and system database.
  6. Open deacom.ini file to confirm configuration.

Restoring Test Databases

  1. Notify users that the Test environment will be down and disable the Test Web Service/Console (and Automation Service/Console if applicable).
  2. From within Microsoft SQL Server Management Studio, if restoring Test databases, right click and delete the existing Test databases.
  3. Right click the Databases folder and click “Restore Database”.
  4. On the General page:
  5. Select “Device”.
  6. Click the “…” button to the right of “Device”.
  7. Click “Add” to bring up a list of all backups.
  8. Select the backup file and click “OK”.
  9. Confirm the Destination Database name and Backup name before proceeding.
  10. Under Destination confirm “Database”. It should have the correct name of the database – for example, “Test” or “TestDocs” or “TestSystem”.
  11. On the File page:
  12. Expand the “Restore As” field.
  13. The first line should have the name of the database and “.mdf”.
  14. The second line is the log file and should have the name of the database with a “_log” or “_1” after it and “.ldf”.
  15. CRITICAL – If refreshing test/training databases, the “Restore As” file names must point to the correct test/training files. The default will be pointing to the original location from when the backup was made.
  16. Click the “…” button next to the .mdf file, select the appropriate path and file, click “OK”.
  17. Click the “…” button next to the .ldf file, select the appropriate path and file, click “OK”.
  18. Confirm the database name in the title bar and the Restore As file names .mdf and .ldf are the correct files.
  19. Click “OK” to finalize the information for the database being restored. This window will remain open while the restore is being completed.
  20. Monitor the status in the Progress bar in the lower left of the window.
  21. Click “OK” once the restore is complete to close the window.
  22. Repeat Steps 1-4 if restoring additional databases. Note that multiple databases can be restored at the same time.
  23. Right click the newly restored database(s) and click “Properties”. On the Files page, confirm the Owner is set to either the “deacom” or “sa” SQL account.
  24. CRITICAL – If refreshing Test\Training system database, it is critical to modify the dxcomp4 table in the system database immediately when refreshing a test\training system database using a production backup (c4_name must be changed to test database name, c4_descrip should be changed to indicate test system and c4_docdb must be changed to test docs database in Version 14.1.x and up). IF THIS CHANGE IS NOT MADE, YOU WILL BE MODIFYING THE PRODUCTION DATA. YOU MUST CHANGE ALL LINES IN THIS TABLE.
  25. Click the “+” sign next to the System database that was just restored.
  26. Click the “+” sign next to the Tables folder.
  27. Locate the “dbo.dxcomp4” table, right click, and click “Edit Top 200 Rows”.
  28. Change the name in the “c4_name” column to the test database name (must be the exact name).
  29. Change the name in the “c4_descrip” column to a test description (*** TEST Company ***).
  30. Change the name in the “c4_docdb” column to the test docs database name (must be the exact name).
  31. If there are multiple companies, change all lines to point to the test files.
  32. Run the current version update that matches the restored database files after every restore in order to reset triggers and other system functions. This should be done after confirming the test system can be logged into and confirmed it is pointing to the correct data files. Use the console to confirm. To update to a later version, follow the instructions on the Completing an Update page.
  33. If using EDI and/or Triggers, continue with the Updating EDI Imports and Triggers section below.
  34. Turn all functionality to test mode (e.g. credit card, FedEx, etc.) by following the steps in the Flipping system functions to Test mode section below.
  35. Restart the Test Web Service/Console (and Automation Service/Console if applicable).

Updating EDI Imports and Triggers

  1. After restoring the Test/Training environment with Production data and updating, all EDI Imports and Triggers need to be modified as they are pointing to Production data rather than Test/Training.
  2. In the Test/Training instance of DEACOM, navigate to System > Maintenance > EDI Imports.
  3. Reset Frequency (mins) to 0 (zero).
  4. Modify (or remove) FTP server to point to test FTP space at your VAN.
  5. Modify the Source path, Success path, and Failure path with correct network paths to the Test/Training EDI Inbound folders.
  6. Save all changes.
  7. In the Test/Training instance of DEACOM, navigate to System > Maintenance > Triggers.
  8. Modify (or remove) FTP server to point to test FTP space at your VAN.
  9. Modify the Destination block with correct network path to the Test/Training EDI Outbound folders.
  10. For users that utilize .NET and the Data Import Scheduler, login to .NET and navigate to Tools > Maintenance > Import Data Schedule and mark each one as inactive.
  11. Save all changes.

Flipping system functions to Test mode

  1. After restoring the Test/Training environment with Production data and updating, all system functions that interact with outside systems (such as PayPal, FedEx, etc.) need to be flipped to Test mode so that test transactions performed do not result in actual charges or shipments.
  2. Set Credit Cards to transact in test mode.
  3. If running in versions prior to 15.04.024 navigate to System > Options > Security tab and check the "Transact In PayPal Test Mode" flag.
  4. If running versions later than 15.04.024, navigate to Accounting > Maintenance > Credit Card Processors and check the "Transact in Test Mode" flag for each applicable record.
  5. Set FedEx and/or UPS to transact in test mode.
  6. Navigate to Sales > Options > APIs tab and check the "Transact In FedEx Test Mode" and "Transact In UPS Test Mode" flags.

Troubleshooting

  • If a message is displayed indicating exclusive access could not be obtained because the database is in use, check the following:
  • Make sure all users are logged out of database at File > View Active Users.
  • Check SQL Activity Monitor to see which users have open connections and kill the processes.
  • Check Server Open Files to determine which users have files open. This can be done using Computer Management or Share and Storage Management depending on the server operating system.

Advanced - Scripting Restores

The below ZIP file contains a set of 4 scripts. They’re meant to speed up the process of backing up and restoring over a system, especially for clients with multiple company databases.

Disclaimer: Review thoroughly and use at your own risk. Restoring databases is ultimately the responsibility of the client, though we can assist. There is no guarantee this will work on your version of DEACOM or with your database server set-up.

Click to download ZIP file (WIP - please contact support if scripts are required)

FAQ and Diagnostic Tips

After restoring a training database, the database still says (Restoring...) next to the name in SQL and I am receiving some messages in DEACOM stating that the database is in the middle of a restore. How do I fix this?

Delete the database with the issue, create a new database with the same name, and restore the new database from the same file that was originally tried.

When trying to log in to a Web version of DEACOM after restoring a training database, I am receiving a prompt indicating "/ refers to a path that does not exist."

One possible cause is a version mismatch. This can happen when refreshing the test environment and it is at a higher DEACOM version. Right-click on the "deacom.exe" file in the training system folder, click the "Details" tab and check the product version number. If the test environment is currently at the same DEACOM version, no additional steps are required, and other causes for the prompt will need to be researched. If the versions are different, the proper update will need to be run before the DEACOM application can be accessed.